package HatterOsztalyok;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.MediaStore;
import android.util.Log;

public class Database extends SQLiteOpenHelper {
	private SQLiteDatabase mdb;
	private ContentResolver resolver;

	/**
	 * Konstruktor
	 */
	public Database(Context context, ContentResolver cr, int version) {
		super(context, "Music", null, version);
		resolver = cr;
	}

	/**
	 * Létrehozza az adatbázist(táblákat és kapcsolatokat)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i("Database", "onCreate meghivva");
		mdb = db;
		mdb.execSQL("CREATE TABLE Music(id int, " + "album varchar(30), "
				+ "artist varchar(30), " + "duration int, " + "size int, "
				+ "title varchar(30), " + "track int, " + "year int)");
		mdb.execSQL("CREATE TABLE Playlist(id int, name varchar(30))");
		mdb.execSQL("CREATE TABLE Conn(mid int references Music(id), pid int references Playlist(id))");
	}

	/**
	 * Betölti a telefonon található zenéket az adatbázisba
	 */
	public void loadMusic() {
		new Thread(new Runnable() {

			public void run() {
				Log.i("Database", "loadMusic meghivva");
				mdb = getWritableDatabase();
				Cursor c = mdb.query("Conn", new String[] { "mid", "pid" },
						null, null, null, null, null);
				long items[][] = null;
				int itemCount = 0;
				if ((c != null) && (!c.moveToFirst())) {
					itemCount = c.getCount();
					if (itemCount > 0) {
						items = new long[itemCount][];
						int k = 0;
						do {
							items[k] = new long[2];
							items[k][0] = c.getLong(c.getColumnIndex("mid"));
							items[k][1] = c.getLong(c.getColumnIndex("pid"));
						} while (c.moveToNext());
					}
				}

				mdb.delete("Conn", null, null);
				mdb.delete("Music", null, null);

				c = resolver
						.query(android.provider.MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
								null, MediaStore.Audio.Media.IS_MUSIC + " = 1",
								null, null);

				if (c == null)
					Log.e("Database", "Query == null");
				if (!c.moveToFirst())
					Log.e("Database", "No query result");

				int idColumn = c.getColumnIndex(MediaStore.Audio.Media._ID);
				int albumColumn = c
						.getColumnIndex(MediaStore.Audio.Media.ALBUM);
				int artistColumn = c
						.getColumnIndex(MediaStore.Audio.Media.ARTIST);
				int durColumn = c
						.getColumnIndex(MediaStore.Audio.Media.DURATION);
				int sizeColumn = c.getColumnIndex(MediaStore.Audio.Media.SIZE);
				int titleColumn = c
						.getColumnIndex(MediaStore.Audio.Media.TITLE);
				int trackColumn = c
						.getColumnIndex(MediaStore.Audio.Media.TRACK);
				int yearColumn = c.getColumnIndex(MediaStore.Audio.Media.YEAR);

				ContentValues values;
				do {
					values = new ContentValues();
					values.put("id", c.getLong(idColumn));
					values.put("album", c.getString(albumColumn));
					values.put("artist", c.getString(artistColumn));
					values.put("duration", c.getLong(durColumn));
					values.put("size", c.getLong(sizeColumn));
					values.put("title", c.getString(titleColumn));
					values.put("track", c.getInt(trackColumn));
					values.put("year", c.getInt(yearColumn));
					mdb.insert("Music", null, values);
				} while (c.moveToNext());

				if (itemCount > 0) {
					for (int i = 0; i < itemCount; i++) {
						values = new ContentValues();
						values.put("mid", items[i][0]);
						values.put("pid", items[i][1]);
						mdb.insert("Conn", null, values);
					}
				}

				Log.i("Database", "Zenek beolvasva");

				close();
			}

		}).start();
	}

	/**
	 * Ha újabb verziószámmal hívjuk meg a konstruktort, akkor ez a metódus
	 * frissíti az adatbázist
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i("Database", "onUpgrade meghivva");
		db.execSQL("DROP TABLE IF EXISTS Music");
		mdb.execSQL("CREATE TABLE Music(id int, " + "album varchar(30), "
				+ "artist varchar(30), " + "duration int, " + "size int, "
				+ "title varchar(30), " + "track int, " + "year int)");
		loadMusic();
	}

	/**
	 * Hozzáad egy üres Playlistet az adatbázishoz
	 * 
	 * @param name
	 *            Az új Playlist neve
	 */
	public void addPlaylist(String name) {
		Log.i("Database", "addPlaylist eleje");
		mdb = getWritableDatabase();

		String[] oszlopok = { "MAX(id) as id" };
		Cursor lekerd = mdb.query("Playlist", oszlopok, null, null, null, null,
				null);
		Log.i("Database", "Lekerdezes utan");
		long ujId = 0;

		if (lekerd == null)
			Log.e("Database", "addPlaylistLekerdezes==null");
		else if (!lekerd.moveToFirst())
			ujId = 1;
		else {
			Log.i("Database", "lekeres elott");
			ujId = lekerd.getLong(lekerd.getColumnIndex("id")) + 1;
		}

		Log.i("Database", "Beszuras elott");
		ContentValues values = new ContentValues();
		values.put("id", ujId);
		values.put("name", name);
		Log.i("Database", "Playlist beszuras elott = " + Long.toString(ujId));
		mdb.insert("Playlist", null, values);
		Log.i("Database", "Beszuras utan");
		mdb.close();
		close();
	}

	/**
	 * Visszatérít egy Cursort az adatbázisban található zenékkel
	 * 
	 * @param source
	 *            0 - albumok, 1 - előadók, 2 - playlist, 3 - minden zene
	 * @param filter
	 *            A szűrés feltétele(pl. az album neve)
	 */
	public Cursor getMusic(int source, String filter) {
		mdb = getReadableDatabase();
		String oszlopok[] = { "id", "album", "artist", "title", "duration" };
		switch (source) {
		case 0:
			return mdb.query("Music", oszlopok, "album='" + filter + "'", null,
					null, null, null);
		case 1:
			return mdb.query("Music", oszlopok, "artist='" + filter + "'",
					null, null, null, null);
		case 2:
			return mdb
					.rawQuery(
							"SELECT Music.id, Music.album, Music.artist, Music.title, Music.duration FROM Music, Playlist, Conn WHERE Music.id=Conn.mid AND Conn.pid=Playlist.id AND Playlist.name='"
									+ filter + "'", null);
		case 3:
			return mdb.query("Music", oszlopok, null, null, null, null, null);
		}
		return null;
	}

	/**
	 * Visszatéríti a zene id-ját
	 * 
	 * @param title
	 *            A keresett zene címe
	 * @return
	 */
	public int getSong(String title) {
		mdb = getReadableDatabase();
		int vissz = 0;
		Cursor lekerd = mdb.rawQuery("SELECT id FROM Music WHERE title='"
				+ title + "'", null);
		if ((lekerd != null) && (lekerd.moveToFirst() != false))
			vissz = lekerd.getInt(lekerd.getColumnIndex("id"));
		mdb.close();
		return vissz;
	}

	/**
	 * Visszatéríti a playlist id-ját
	 * 
	 * @param name
	 *            A keresett playlist neve
	 * @return
	 */
	public int getPlaylist(String name) {
		mdb = getReadableDatabase();
		int vissz = 0;
		Cursor lekerd = mdb.rawQuery("SELECT id FROM Playlist WHERE name='"
				+ name + "'", null);
		if ((lekerd != null) && (lekerd.moveToFirst() != false))
			vissz = lekerd.getInt(lekerd.getColumnIndex("id"));
		mdb.close();
		return vissz;
	}

	/**
	 * Hozzáadja a Conn táblához a mid és pid értékeket. Ezzel hozzáad egy
	 * számot az egyik playlisthez
	 * 
	 * @param mid
	 *            Zene id-ja
	 * @param pid
	 *            Playlist id-ja
	 */
	public void insertToConn(int mid, int pid) {
		mdb = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("mid", mid);
		values.put("pid", pid);
		mdb.insert("Conn", null, values);
		mdb.close();
		close();
	}
}
